﻿Imports System.Net
Imports System.Net.Sockets
Imports MySql.Data.MySqlClient

Module DbConnect
    Public errMsg As String

    Public Function CheckDBServer(ByVal SQLHost As String, ByVal SQLPort As Integer) As Boolean
        Try
            Dim IPHost As New IPHostEntry()
            IPHost = Dns.GetHostEntry(SQLHost)

            Dim IPAddr As IPAddress = IPHost.AddressList(0)

            Dim TcpCli As New TcpClient()
            TcpCli.Connect(IPAddr, SQLPort)
            TcpCli.Close()

            Return True
        Catch
            Return False
        End Try
    End Function

    ' Retrieve a connection string by specifying the providerName.
    ' Assumes one connection string per provider in the config file.
    Public Function GetConnectionStringByProvider( _
        ByVal providerName As String) As String

        'Return Nothing on failure.
        Dim returnValue As String = Nothing

        ' Get the collection of connection strings.
        Dim settings As ConnectionStringSettingsCollection = _
            ConfigurationManager.ConnectionStrings

        ' Walk through the collection and return the first 
        ' connection string matching the providerName.
        If Not settings Is Nothing Then
            For Each cs As ConnectionStringSettings In settings
                If cs.ProviderName = providerName Then
                    returnValue = cs.ConnectionString
                    Exit For
                End If
            Next
        End If

        Return returnValue
    End Function

    Public Function executeBatch(ByVal sql As IEnumerable) As Boolean
        Dim con As MySql.Data.MySqlClient.MySqlConnection = New MySqlConnection(GetConnectionStringByProvider("MySql.Data.MySqlClient"))
        con.Open()
        ' Start a local transaction.
        Dim myTrans As MySqlTransaction = con.BeginTransaction()

        ' Enlist the command in the current transaction.
        Dim myCommand As MySqlCommand = con.CreateCommand()
        myCommand.Transaction = myTrans

        Try
            For Each s As String In sql
                myCommand.CommandText = s
                myCommand.ExecuteNonQuery()
            Next
            myTrans.Commit()
            Return True
        Catch ex As Exception
            Try
                myTrans.Rollback()
            Catch er As MySqlException
                If Not myTrans.Connection Is Nothing Then
                    errMsg = "An exception of type " & er.Message.ToString & _
                                      " was encountered while attempting to roll back the transaction."
                End If
            End Try

            errMsg = ex.Message.ToString
            'errMsg = "Neither record was written to database."
            Return False
        Finally
            con.Close()
        End Try

    End Function

End Module
